<?php
class Application_Model_Mapper_LEMData extends Application_Model_Mapper_Abstract
{
	/**
	* Get Db table object
	*
	* @return Model_DbTable_lEMData
	*/
	protected $_dbName;
	public function __construct()
	{
		parent::__construct();
		$this->_dbName = $this->_db_ovis_name.'.lemdata';
	}

	public function getDbTable()
	{
		if (NULL === $this->_dbTable) {
			$this->setDbTable('Application_Model_DbTable_Inventory');
		}
		return $this->_dbTable;
	}

	/**
	* @param Zend_Db_Table_Row_Abstract $row Db table row
	*
	* @return Model_lEMData|null lEMData object or NULL
	* @throws Exception
	*/
	public function mapData($row)
	{
		if (is_null($row)) {
			return NULL;
		}
		if (!$row instanceof Zend_Db_Table_Row) {
			throw new Exception('Invalid table row provided');
		}
		$entry = new Application_Model_LEMData();
		$entry->setDataId($row['dataid'])
		->sethopC($row['hop_c'])
		->setFirstHop($row['first_hop'])
		->setLastHop($row['last_hop'])
		->setRssi($row['rssi'])
		->setaecP1($row['aec_p1'])
		->setaecP2($row['aec_p2'])
		->setaecP3($row['aec_p3'])
		->setaecPs($row['aec_ps'])
		->setRecP1($row['rec_p1'])
		->setRecP2($row['rec_p2'])
		->setRecP3($row['rec_p3'])
		->setRecPs($row['rec_ps'])
		->setApecP1($row['apec_p1'])
		->setApecP2($row['apec_p2'])
		->setApecP3($row['apec_p3'])
		->setApecPs($row['apec_ps'])
		->setEcTimestamp($row['ec_timestamp'])
		->setFreq($row['freq'])
		->setRiTimestamp($row['ri_timestamp'])
		->setRiAeP1($row['ri_ae_p1'])
		->setRiAeP2($row['ri_ae_p2'])
		->setRiAeP3($row['ri_ae_p3'])
		->setRiAePs($row['ri_ae_ps'])
		->setRiReP1($row['ri_re_p1'])
		->setRiReP2($row['ri_re_p2'])
		->setRiReP3($row['ri_re_p3'])
		->setRiRePs($row['ri_re_ps'])
		->setRiApeP1($row['ri_ape_p1'])
		->setRiApeP2($row['ri_ape_p2'])
		->setRiApeP3($row['ri_ape_p3'])
		->setRiApePs($row['ri_ape_ps'])
		->setMcIP1($row['mc_i_p1'])
		->setMcIP2($row['mc_i_p2'])
		->setMcIP3($row['mc_i_p3'])
		->setMvIP1($row['mv_i_p1'])
		->setMvIP2($row['mv_i_p2'])
		->setMvIP3($row['mv_i_p3'])
		->setSuppVolt($row['supp_volt'])
		->setHardware($row['hardware']);
		return $entry;
	}

	/**
	* Save lEMData object in database
	*
	* @param Model_lEMData $lemdata
	*
	* @return int The primary key of the row inserted or the number of rows updated
	*/
	public function save($lemdata)
	{
		parent::save($lemdata);
		$data = array(
				'dataid'               => $lemdata->getDataId(),
				'hop_c'                => $lemdata->gethopC(),
				'first_hop'            => $lemdata->getFirstHop(),
				'last_hop'             => $lemdata->getLastHop(),
				'rssi'                 => $lemdata->getRssi(),
				'aec_p1'               => $lemdata->getaecP1(),
				'aec_p2'               => $lemdata->getaecP2(),
				'aec_p3'               => $lemdata->getaecP3(),
				'aec_ps'               => $lemdata->getaecPs(),
				'rec_p1'               => $lemdata->getRecP1(),
				'rec_p2'               => $lemdata->getRecP2(),
				'rec_p3'               => $lemdata->getRecP3(),
				'rec_ps'               => $lemdata->getRecPs(),
				'apec_p1'              => $lemdata->getApecP1(),
				'apec_p2'              => $lemdata->getApecP2(),
				'apec_p3'              => $lemdata->getApecP3(),
				'apec_ps'              => $lemdata->getApecPs(),
				'ec_timestamp'         => $lemdata->getEcTimestamp(),
				'freq'                 => $lemdata->getFreq(),
				'ri_timestamp'         => $lemdata->getRiTimestamp(),
				'ri_ae_p1'             => $lemdata->getRiAeP1(),
				'ri_ae_p2'             => $lemdata->getRiAeP2(),
				'ri_ae_p3'             => $lemdata->getRiAeP3(),
				'ri_ae_ps'             => $lemdata->getRiAePs(),
				'ri_re_p1'             => $lemdata->getRiReP1(),
				'ri_re_p2'             => $lemdata->getRiReP2(),
				'ri_re_p3'             => $lemdata->getRiReP3(),
				'ri_re_ps'             => $lemdata->getRiRePs(),
				'ri_ape_p1'            => $lemdata->getRiApeP1(),
				'ri_ape_p2'            => $lemdata->getRiApeP2(),
				'ri_ape_p3'            => $lemdata->getRiApeP3(),
				'ri_ape_ps'            => $lemdata->getRiApePs(),
				'mc_i_p1'              => $lemdata->getMcIP1(),
				'mc_i_p2'              => $lemdata->getMcIP2(),
				'mc_i_p3'              => $lemdata->getMcIP3(),
				'mv_i_p1'              => $lemdata->getMvIP1(),
				'mv_i_p2'              => $lemdata->getMvIP2(),
				'mv_i_p3'              => $lemdata->getMvIP3(),
				'supp_volt'            => $lemdata->getSuppVolt(),
				'hardware'             => $lemdata->getHardware()
		);
		try {
			if (NULL === ($id = $lemdata->getDataId())) {
				unset($data['dataid']);
				$lemdata->setDataId($this->getDbTable()->insert($data));
				return $lemdata->getDataId();
			} else {
				return $this->getDbTable()->update($data, array('dataid = ?' => $id));
			}
		} catch (Exception $e) {
			Sss_Ultility_Common::logger()->log($e, Zend_Log::ERR);
		}
	}

	/**
	* Fetch all lEMData objects
	*
	* @return Model_lEMData[] Array of lEMData objects
	*/
	public function fetchAll()
	{
		$resultSet = $this->getDbTable()->fetchAll();
		$entries   = array();
		foreach ($resultSet as $row) {
			$entries[] = $this->mapData($row);
		}
		return $entries;
	}

	/**
	* put your comment there...
	*
	* @param mixed $lemdataId
	* @return Model_DbTable_lEMData
	*/
	public function deleteRow($lemdataId)
	{
		return $this->getDbTable()->delete(array('dataid = ?' => $lemdataId));
	}
	/**
	* Finds a lEMData by Id
	*
	* @param  int $lemdataId Channel Id
	*
	* @return Model_lEMData lEMData object
	*/
	public function findById($lemdataId)
	{
		$select = $this->getDbTable()->select()->where('dataid = ?', $lemdataId);
		$row    = $this->getDbTable()->fetchRow($select);
		return $this->mapData($row);
	}

	public function getDataInPeriodTime($hardwareId, $typeCode, $fromDateTime, $toDateTime, $unitCode)
	{
		$column = $typeCode;
		$where  = array();
		$where[] = 'hardware='.$hardwareId;

		if ($column == 'aec_p1' || $column == 'aec_p2' || $column == 'aec_p3' || $column == 'aec_ps' || $column == 'apec_p1' || $column == 'apec_p2' || $column == 'apec_p3' || $column == 'apec_ps' || $column == 'rec_p1' || $column == 'rec_p2' || $column == 'rec_p3' || $column == 'rec_ps') {
			$where[] = "ec_timestamp>='".$fromDateTime."'";
			$where[] = "ec_timestamp<='".$toDateTime."'";
		} else {
			$where[] = "ri_timestamp>='".$fromDateTime."'";
			$where[] = "ri_timestamp<='".$toDateTime."'";
		}

		$maxValueQuery = "SELECT MAX($column) AS `MaxValue` FROM " . $this->_dbName . " WHERE " .  implode(' AND ', $where);
		$maxRes = $this->fetchObject($maxValueQuery, false);
		$tmpUnitVal = 1;
		if (isset($maxRes->MaxValue) && !is_null($maxRes->MaxValue)) {
			switch ($unitCode) {
				case 'Wh':
					$arrUnitCodeValue = Shepherd_ChartUnit::$WattHour;
					break;
				case 'VARh':
					$arrUnitCodeValue = Shepherd_ChartUnit::$VoltAmpereReactiveHour;
					break;
				case 'VAh':
					$arrUnitCodeValue = Shepherd_ChartUnit::$VoltAmpereHour;
					break;
			}
			$maxUnitValue = $maxRes->MaxValue;
			$crrUnit = $unitCode;
			foreach ($arrUnitCodeValue as $k=>$crrVal) {
				if ($maxUnitValue >= $crrVal) {
					$tmpUnitVal = $crrVal;
					$crrUnit = $k;
				}
			}
		}
		$avg = 'AVG(' . $column . '/' . $tmpUnitVal . ') ';
		$query = "SELECT $avg AS `value` FROM " . $this->_dbName;
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		return array('value' => $this->fetchObject($query . $where)->value, 'unitcode'=>$crrUnit);;
	}

	public function getDataByHardware($hardwareId, $valueType, $arrayData)
	{
		$fromValue = $arrayData['from_value'];
		$toValue = $arrayData['to_value'];
		$fromDateTime = $arrayData['from_datetime'];
		$toDateTime = $arrayData['to_datetime'];
		$unitCode = $arrayData['unitcode'];
		$isAvg = $arrayData['average_value'];
		$valueTypeQuery = 'SELECT typecode FROM ' . $this->_db_ovis_name . '.systemtypes WHERE systemtypeid = ' . $valueType;
		$systemType = $this->fetchObject($valueTypeQuery);
		if (is_null($systemType) || empty($systemType)) {
			return null;
		}
		$column = $systemType->typecode;
		$where  = array();
		$where[] = 'hardware='.$hardwareId;

		if($fromDateTime != $toDateTime)
		{
			if ($column == 'aec_p1' || $column == 'aec_p2' || $column =='aec_p3' || $column == 'aec_ps' || $column == 'apec_p1' || $column == 'apec_p2' || $column == 'apec_p3' || $column == 'apec_ps' || $column == 'rec_p1' || $column == 'rec_p2' || $column == 'rec_p3' || $column == 'rec_ps') {
				$where[] = "ec_timestamp>='".$fromDateTime.":00 '";
				$where[] = "ec_timestamp<='".$toDateTime.":59 '";
			} else {
				$where[] = "ri_timestamp>='".$fromDateTime.":00 '";
				$where[] = "ri_timestamp<='".$toDateTime.":59 '";
			}
		}

		$maxValueQuery = "SELECT MAX($column) AS `MaxValue` FROM " . $this->_dbName. " WHERE " .  implode(' AND ', $where);
		$maxRes = $this->fetchObject($maxValueQuery, false);
		$tmpUnitVal = 1;
		$arrUnitCodeValue = array();
		$crrUnit = '';
		if (isset($maxRes->MaxValue) && !is_null($maxRes->MaxValue)) {
			switch ($unitCode) {
				case 'Wh':
					$arrUnitCodeValue = Sss_Controller_Shepherd_ChartUnit::$WattHour;
					break;
				case 'VARh':
					$arrUnitCodeValue = Sss_Controller_Shepherd_ChartUnit::$VoltAmpereReactiveHour;
					break;
				case 'VAh':
					$arrUnitCodeValue = Sss_Controller_Shepherd_ChartUnit::$VoltAmpereHour;
					break;
			}
			$maxUnitValue = $maxRes->MaxValue;
			$crrUnit = $unitCode;
			foreach ($arrUnitCodeValue as $k=>$crrVal) {
				if ($maxUnitValue >= $crrVal) {
					$tmpUnitVal = $crrVal;
					$crrUnit = $k;
				}
			}
		}

		if($fromValue != null && $toValue != null && $toValue != $fromValue)
		{
			$where[] = $column.'>='.($fromValue*$tmpUnitVal) ;
			$where[] = $column.'<='.($toValue*$tmpUnitVal);
		}

		$dateField = '';
		if ($column == 'aec_p1' || $column == 'aec_p2' || $column == 'aec_p3' || $column == 'aec_ps' || $column == 'apec_p1' || $column == 'apec_p2' || $column == 'apec_p3' || $column == 'apec_ps' || $column == 'rec_p1' || $column == 'rec_p2' || $column == 'rec_p3' || $column == 'rec_ps') {
			$dateField = 'ec_timestamp';
		} else {
			$dateField = 'ri_timestamp';
		}
	
		if ($isAvg) {
			$avg = "CONCAT('[''',DATE_FORMAT(" . $dateField . ", '%m/%d/%Y %H:%i:%S') ,'''' ,',' ,ROUND(AVG(" . $systemType->typecode . "/" . $tmpUnitVal . "), 2), ']') as `value`";
		} else {
			$avg = "CONCAT('[''',DATE_FORMAT(" . $dateField . ", '%m/%d/%Y %H:%i:%S') ,'''' ,',' ,ROUND(MIN(" . $systemType->typecode . "/" . $tmpUnitVal . "), 2), ']') as `value`";
		}
			
		$select = '';
		$groupby = '';
		$dateValue = "DATE_FORMAT($dateField, '%m/%d/%Y %H:%i:%S') ";
		$select = "
				SELECT 
					" . $dateValue . "  AS `created_date`, CONCAT('[''',DATE_FORMAT(" . $dateField . ", '%m/%d/%Y %H:%i:%S') ,'''' ,',' ,ROUND(" . $column . "/" . $tmpUnitVal . ", 2), ']') AS `value` 
				FROM 
					" . $this->_dbName;
		$groupby = '';
	
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';
		$results = $this->loadAll($select.' '.$where.' '.$groupby, false);
		return is_null($results) ? null : array('value' => $results, 'unitcode'=>$crrUnit);
	}
	
	public function getHardwareLemDataMaxCreated($hardwareId,$typeCode = null)
	{
		$where =array();
		if(!empty($hardwareId)) {
			$where[] = 'data.hardware = '.$hardwareId;
		}
		if(!empty($typeCode)) {
			$where[] = $typeCode.' is not null';
		}
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$query = "
					SELECT 
						data.hardware,MAX(data.ec_timestamp) as dateMax 
					FROM 
						" . $this->_dbName . " as data "
					.$where;
		$rowAll  = $this->loadAll($query , false);
		if(is_null($rowAll) || empty($rowAll)){
			return null;
		}
		return $rowAll;
	}
	public function getHardwareLemDataFromToLemData($hardwareId,$start,$end,$typeCode = null)
	{
		$typeCodeNotNull = '';
		if( !empty($typeCode) ) {
			$typeCodeNotNull = ' AND '.$typeCode.' is not null';
		}
		$query = "
					SELECT 
						hardware,ec_timestamp 
					FROM 
						" . $this->_dbName . " 
					WHERE 
						hardware = ".$hardwareId." 
					AND 
						ec_timestamp BETWEEN '".$start."' 
					AND 
						'".$end."'".$typeCodeNotNull." 
					ORDER BY 
						ec_timestamp ";
	
		$rowAll = $this->loadAll($query,false);
		if(is_null($rowAll) || empty($rowAll)){
			return null;
		}
		return $rowAll;
	}
	public function getDataMinMaxByHardware($hardwareId, $valueType, $arrayData)
	{
		$fromValue = $arrayData['from_value'];
		$toValue = $arrayData['to_value'];
		$fromDateTime = $arrayData['from_datetime'];
		$toDateTime = $arrayData['to_datetime'];
		$unitCode = $arrayData['unitcode'];
		$isAvg = $arrayData['average_value'];
		$valueTypeQuery = '
					SELECT 
						typecode 
					FROM 
						' . $this->_db_ovis_name . '.systemtypes 
					WHERE 
						systemtypeid = ' . $valueType;
		$systemType = $this->fetchObject($valueTypeQuery);
		if (is_null($systemType) || empty($systemType)) {
			return null;
		}
		$column = $systemType->typecode;
		$where  = array();
		$where[] = 'hardware='.$hardwareId;
	
		if($fromDateTime != $toDateTime)
		{
			if ($column == 'aec_p1' || $column == 'aec_p2' || $column == 'aec_p3' || $column == 'aec_ps' || $column == 'apec_p1' || $column == 'apec_p2' || $column == 'apec_p3' || $column == 'apec_ps' || $column == 'rec_p1' || $column == 'rec_p2' || $column == 'rec_p3' || $column == 'rec_ps') {
				$where[] = "ec_timestamp>='".$fromDateTime.":00 '";
				$where[] = "ec_timestamp<='".$toDateTime.":59 '";
			} else {
				$where[] = "ri_timestamp>='".$fromDateTime.":00 '";
				$where[] = "ri_timestamp<='".$toDateTime.":59 '";
			}
		}
		$maxValueQuery = "
							SELECT 
								MAX($column) AS `MaxValue` 
							FROM 
								" . $this->_dbName . 
							" WHERE 
								" .  implode(' AND ', $where);
		$maxRes = $this->fetchObject($maxValueQuery, false);
		$tmpUnitVal = 1;
		$arrUnitCodeValue = array();
		$crrUnit = '';
		if (isset($maxRes->MaxValue) && !is_null($maxRes->MaxValue)) {
			switch ($unitCode) {
				case 'Wh':
					$arrUnitCodeValue = Sss_Controller_Shepherd_ChartUnit::$WattHour;
					break;
				case 'VARh':
					$arrUnitCodeValue = Sss_Controller_Shepherd_ChartUnit::$VoltAmpereReactiveHour;
					break;
				case 'VAh':
					$arrUnitCodeValue = Sss_Controller_Shepherd_ChartUnit::$VoltAmpereHour;
					break;
			}
				
			$maxUnitValue = $maxRes->MaxValue;
			$crrUnit = $unitCode;
			foreach ($arrUnitCodeValue as $k=>$crrVal) {
				if ($maxUnitValue >= $crrVal) {
					$tmpUnitVal = $crrVal;
					$crrUnit = $k;
				}
			}
		}
		if($fromValue != null && $toValue != null && $toValue != $fromValue)
		{
			$where[] = $column.'>='.($fromValue*$tmpUnitVal) ;
			$where[] = $column.'<='.($toValue*$tmpUnitVal);
		}
		$select = '';
		$groupby = '';
		$orderby = 'ORDER BY '.$column.' DESC';
		$select = "
					SELECT 
						MAX(" . $column . "*1/" . $tmpUnitVal . ") as `valueMax`,MIN(" . $column . "/" . $tmpUnitVal . "*1) as `valueMin` 
					FROM 
						" . $this->_dbName;
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';
		return $this->fetchObject($select.' '.$where.' '.$groupby.' '.$orderby, false);
	}
}

